HiSEN

Java - 利用apache POI读取Excel（xls,xlsx,2003,2007)

发表于 | 分类于

一个简单的工具类，更多java小练习：https://github.com/hisenyuan/IDEAPractice

一、添加依赖

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
<!-- POI start -->
<dependency>
  <groupId>org.apache.poi</groupId>
  <artifactId>poi</artifactId>
  <version>3.9</version>
</dependency>
<dependency>
  <groupId>org.apache.poi</groupId>
  <artifactId>poi-ooxml</artifactId>
  <version>3.9</version>
</dependency>
<dependency>
  <groupId>commons-collections</groupId>
  <artifactId>commons-collections</artifactId>
  <version>3.2.2</version>
</dependency>
<!-- POI end -->

二、测试类

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
package com.hisen.jars.poi;

import java.io.File;
import java.io.IOException;
import java.util.Arrays;
import java.util.List;

/**
 * @author hisenyuan
 * @time 2018/1/12 17:43
 * @description 测试读取
 */
public class TestPoiExcelUtil {

  public static void main(String[] args) {
    File file = new File("C:\\work\\document\\银行信息.xlsx");
    try {
      // 每一个excelData为一行数据（存放在数组）
      List<String[]> excelData = POIExcelUtil.readExcel(file);
      for (String[] data:excelData) {
        System.out.println(Arrays.toString(data));
      }
    } catch (IOException e) {
      e.printStackTrace();
    }
  }

}

三、工具类代码

完整类：POIExcelUtil.java

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
package com.hisen.jars.poi;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;
import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

/**
 * @author hisenyuan
 * @time 2018/1/12 16:35
 * @description 利用POI读取excel表格
 */
public class POIExcelUtil {

  private static Logger logger = Logger.getLogger(POIExcelUtil.class);
  private final static String XLS = "xls";
  private final static String XLSX = "xlsx";

  public static List<String[]> readExcel(File file) throws IOException {
    // 检查文件
    checkFile(file);
    Workbook workBook = getWorkBook(file);
    // 返回对象,每行作为一个数组，放在集合返回
    ArrayList<String[]> rowList = new ArrayList<>();
    if (null != workBook) {
      for (int sheetNum = 0; sheetNum < workBook.getNumberOfSheets(); sheetNum++) {
        // 获得当前sheet工作表
        Sheet sheet = workBook.getSheetAt(sheetNum);
        if (sheet == null) {
          continue;
        }
        // 获得当前sheet的开始行
        int firstRowNum = sheet.getFirstRowNum();
        // 获得当前sheet的结束行
        int lastRowNum = sheet.getLastRowNum();
        // 循环所有行(第一行为标题)
        for (int rowNum = firstRowNum; rowNum < lastRowNum; rowNum++) {
          // 获得当前行
          Row row = sheet.getRow(rowNum);
          if (row == null) {
            continue;
          }
          // 获得当前行开始的列
          short firstCellNum = row.getFirstCellNum();
          // 获得当前行的列数
          int lastCellNum = row.getPhysicalNumberOfCells();
          String[] cells = new String[row.getPhysicalNumberOfCells()];
          // 循环当前行
          for (int cellNum = firstCellNum; cellNum < lastCellNum; cellNum++) {
            Cell cell = row.getCell(cellNum);
            cells[cellNum] = getCellValue(cell);
          }
          rowList.add(cells);
        }
      }
    }
    return rowList;
  }
}